package com.infore.statisticsAndAlarm.repository;

import java.util.Date;
import java.util.List;
import java.util.Map;

import javax.transaction.Transactional;

import org.springframework.data.jpa.repository.JpaRepository;
import org.springframework.data.jpa.repository.JpaSpecificationExecutor;
import org.springframework.data.jpa.repository.Modifying;
import org.springframework.data.jpa.repository.Query;
import org.springframework.data.repository.query.Param;
import org.springframework.stereotype.Repository;

import com.infore.statisticsAndAlarm.entity.DayData;

@Repository
public interface IDayDataRepository extends JpaRepository<DayData, String>, JpaSpecificationExecutor<DayData> {

	// 通过站点，因子，时间段，得到每一条记录
	List<DayData> findByStationCodeInAndFactorCodeInAndDataTimeBetweenOrderByDataTimeAsc(List<String> stationCode,
			List<String> factorCode, Date start, Date end);

	// 通过站点，因子，时间段，得到每一条记录,返回listMap---用于单点自动站报表
	@Query(value = "select t.station_code as MN,t.factor_code as CODE,t.data_time as TIME,t.data_value_avg as VALUE"
			+ " from DAY_DATA t where (t.station_code in (:mnList))"
			+ " and (t.factor_code in (:projectCodes)) and (t.data_time between :startDate and :endDate)"
			+ " order by t.data_time ", nativeQuery = true)
	List<Map<String, Object>> audioSingReport(@Param("startDate") Date startDate, @Param("endDate") Date endDate,
			@Param("projectCodes") List<String> projectCodes, @Param("mnList") List<String> mnList);

	// 通过站点，因子，时间段，得到每一条记录,返回listMap---用于多站自动站报表
	@Query(value = "select t.station_code as MN, t.factor_code as CODE , to_char( avg(t.data_value_avg),'fm999999990.999999999') as VALUE ,to_char(sum(t.real_num)) as COUNT "
			+ "from day_data t where (t.station_code in (:mnList)) and (t.factor_code in (:projectCodes)) and (t.data_time between :startDate and :endDate) group by t.station_code,t.factor_code order by t.station_code", nativeQuery = true)
	List<Map<String, Object>> audioMoreReport(@Param("startDate") Date startDate, @Param("endDate") Date endDate,
			@Param("projectCodes") List<String> projectCodes, @Param("mnList") List<String> mnList);

	// 通过因子和站点分组查询，并按时间筛选，得到站点信息，因子信息，每个因子的值
//	@Query(value = "select t.factor_code as factor,t.station_code as station,to_char( round(avg(t.data_value_avg), 3),'fm999999990.999999999') as avg,concat( to_char(round(avg(t.data_value_min), 3),'fm999999990.999999999'),concat('-', to_char(round(avg(t.data_value_max), 3),'fm999999990.999999999') )) as scope"
//			+ "  from DAY_DATA t where t.data_time between :startDate and :endDate and t.factor_code in(:factorList) group by t.factor_code,t.station_code", nativeQuery = true)
//	List<Map<String, Object>> queryInfoGroupBy(@Param("startDate") Date startDate, @Param("endDate") Date endDate,
//			@Param("factorList") List<String> factorList);
	@Query(value = "select t.factor_code as factor,t.station_code as station,to_char(avg(t.data_value_avg), 'fm999999990.999999999') as avg,to_char(avg(t.data_value_min), 'fm999999990.999999999') as min,to_char(avg(t.data_value_max), 'fm999999990.999999999') as max " + 
			"from DAY_DATA t where t.data_time between :startDate and :endDate and t.factor_code in (:factorList) group by t.factor_code, t.station_code", nativeQuery = true)
	List<Map<String, Object>> queryInfoGroupBy(@Param("startDate") Date startDate, @Param("endDate") Date endDate,
			@Param("factorList") List<String> factorList);
	
	/**
    * @Title: getCountWeekData
    * @Description: 查询天表,统计各站的周数据
    * @param startTime
    * @param endTime
    * @return    参数
    * @return List<Map<String,Object>>    返回类型
    * @throws
     */
	@Query(nativeQuery = true,value = "SELECT STATION_CODE,FACTOR_CODE," + 
            "      TO_CHAR(DATA_TIME,'yyyy') YEAR,TO_CHAR(DATA_TIME,'iw') WEEK,AVG(DATA_VALUE_AVG) DATA_VALUE_AVG," + 
            "      MAX(DATA_VALUE_AVG) DATA_VALUE_MAX,MIN(DATA_VALUE_AVG) DATA_VALUE_MIN,SUM(DATA_VALUE_AVG) TOTAL_VALUE,COUNT(1) REAL_NUM " + 
            "      FROM DAY_DATA WHERE TO_CHAR(DATA_TIME,'yyyy') IN (:yearTimeList) AND TO_CHAR(DATA_TIME,'iw') IN (:weekTimeList) " + 
            "GROUP BY STATION_CODE,FACTOR_CODE,TO_CHAR(DATA_TIME,'yyyy'),TO_CHAR(DATA_TIME,'iw') ")
    public List<Map<String, Object>> getCountWeekData(@Param("yearTimeList")List<String> yearTimeList, @Param("weekTimeList")List<String> weekTimeList);
	
	/**
    * @Title: getCountMonthData
    * @Description: 查询天表,统计各站的月数据
    * @param startTime
    * @param endTime
    * @return    参数
    * @return List<Map<String,Object>>    返回类型
    * @throws
     */
    @Query(nativeQuery = true,value = "SELECT STATION_CODE,FACTOR_CODE," + 
            "      TO_CHAR(DATA_TIME,'yyyy-MM') TIME,AVG(DATA_VALUE_AVG) DATA_VALUE_AVG," + 
            "      MAX(DATA_VALUE_AVG) DATA_VALUE_MAX,MIN(DATA_VALUE_AVG) DATA_VALUE_MIN,SUM(DATA_VALUE_AVG) TOTAL_VALUE,COUNT(1) REAL_NUM " + 
            "      FROM DAY_DATA WHERE TO_CHAR(DATA_TIME,'yyyy-MM') IN (:monthTimeList) " + 
            "GROUP BY STATION_CODE,FACTOR_CODE,TO_CHAR(DATA_TIME,'yyyy-MM') ")
    public List<Map<String, Object>> getCountMonthData(@Param("monthTimeList")List<String> monthTimeList);
    
    /**
    * @Title: getSiteCountQuarterData
    * @Description: 查询天表,统计各站的季度数据
    * @param startTime
    * @param endTime
    * @return    参数
    * @return List<Map<String,Object>>    返回类型
    * @throws
     */
    @Query(nativeQuery = true,value = "SELECT STATION_CODE,FACTOR_CODE," + 
            "      TO_CHAR(DATA_TIME,'yyyy') YEAR,TO_CHAR(DATA_TIME,'Q') QUARTER,AVG(DATA_VALUE_AVG) DATA_VALUE_AVG," + 
            "      MAX(DATA_VALUE_AVG) DATA_VALUE_MAX,MIN(DATA_VALUE_AVG) DATA_VALUE_MIN,SUM(DATA_VALUE_AVG) TOTAL_VALUE,COUNT(1) REAL_NUM " + 
            "      FROM DAY_DATA WHERE TO_CHAR(DATA_TIME,'yyyy') IN (:yearTimeList) " + 
            "      AND TO_CHAR(DATA_TIME,'Q') IN (:quarterTimeList) " +
            "GROUP BY STATION_CODE,FACTOR_CODE," + 
            "TO_CHAR(DATA_TIME,'yyyy'),TO_CHAR(DATA_TIME,'Q') ")
    public List<Map<String, Object>> getCountQuarterData(@Param("yearTimeList") List<String> yearTimeList,
            @Param("quarterTimeList") List<String> quarterTimeList);

    /**
    * @Title: getSiteCountYearData
    * @Description: 查询天表,统计各站的年数据
    * @param startTime
    * @param endTime
    * @return    参数
    * @return List<Map<String,Object>>    返回类型
    * @throws
     */
    @Query(nativeQuery = true,value = "SELECT STATION_CODE,FACTOR_CODE," + 
            "      TO_CHAR(DATA_TIME,'yyyy') YEAR,AVG(DATA_VALUE_AVG) DATA_VALUE_AVG," + 
            "      MAX(DATA_VALUE_AVG) DATA_VALUE_MAX,MIN(DATA_VALUE_AVG) DATA_VALUE_MIN,SUM(DATA_VALUE_AVG) TOTAL_VALUE,COUNT(1) REAL_NUM " + 
            "      FROM DAY_DATA WHERE TO_CHAR(DATA_TIME,'yyyy') IN (:yearTimeList) " + 
            "GROUP BY STATION_CODE,FACTOR_CODE," + 
            "TO_CHAR(DATA_TIME,'yyyy') ")
    public List<Map<String, Object>> getCountYearData(@Param("yearTimeList")List<String> yearTimeList);
    
    /**
     * @Title: updateDayWeekData
     * @Description: 更新已统计后的天表周数据标识
     * @param flag
     * @return    参数
     * @return int    返回类型
     * @throws
      */
     @Modifying
     @Transactional 
     @Query(nativeQuery = true,value = "UPDATE DAY_DATA SET WEEK_FLAG = :flag WHERE WEEK_FLAG = 0 ")
     int updateDayWeekData(@Param("flag")Integer flag);
    
    /**
     * @Title: updateDayMonthData
     * @Description: 更新已统计后的天表月数据标识
     * @param flag
     * @return    参数
     * @return int    返回类型
     * @throws
      */
     @Modifying
     @Transactional 
     @Query(nativeQuery = true,value = "UPDATE DAY_DATA SET MONTH_FLAG = :flag WHERE MONTH_FLAG = 0 ")
     int updateDayMonthData(@Param("flag")Integer flag);
     
     /**
     * @Title: updateDayQuarterData
     * @Description: 更新已统计后的天表季度数据标识
     * @param flag
     * @return    参数
     * @return int    返回类型
     * @throws
      */
     @Modifying
     @Transactional 
     @Query(nativeQuery = true,value = "UPDATE DAY_DATA SET QUARTER_FLAG = :flag WHERE QUARTER_FLAG = 0 ")
     int updateDayQuarterData(@Param("flag")Integer flag);
     
     /**
     * @Title: updateDayYearData
     * @Description: 更新已统计后的天表年数据标识
     * @param flag
     * @return    参数
     * @return int    返回类型
     * @throws
      */
     @Modifying
     @Transactional 
     @Query(nativeQuery = true,value = "UPDATE DAY_DATA SET YEAR_FLAG = :flag WHERE YEAR_FLAG = 0 ")
     int updateDayYearData(@Param("flag")Integer flag);
	
}